﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DAO
{
    public class ProductDao
    {
        public ProductDao()
        {
          new SqlHelper();
        }
        public bool insertProduct(string name, float price, string notes, int usrId, int unitId, string special, string producer)
        {
            return SqlHelper.ExecuteNonQuery("INSERT_PRODUCT", CommandType.StoredProcedure,
                "@NAME", name,
                "@PRICE", price,
                "@NOTES", notes,
                "@USR_ID", usrId,
                "@UNIT_ID", unitId,
                "@SPECIAL", special,
                "@PRODUCER", producer);
        }

        public DataTable getProducts(string name)
        {
            return SqlHelper.ExecuteDataTable("GET_PRODUCTS", CommandType.StoredProcedure,
                "@NAME", name);
        }

        
        public DataTable getProductsForSale(string name)
        {
            return SqlHelper.ExecuteDataTable("GET_PRODUCTS_FOR_SALE", CommandType.StoredProcedure,
                "@NAME", name);
        } 
        public DataTable getProperties(string name)
        {
            return SqlHelper.ExecuteDataTable("GET_PROPERTIES", CommandType.StoredProcedure,
                "@NAME", name);
        }

        /*
        public bool updateProduct(int id, string name, float price, string notes, int usrId, string special)
        {
            return SqlHelper.ExecuteNonQuery("INSERT_PRODUCT", CommandType.StoredProcedure,
                "@ID", id,
                "@NAME", name,
                "@PRICE", price,
                "@NOTES", notes,
                "@USR_ID", usrId,
                "@SPECIAL", special);
        }
        */
        public string getUnitNameofProduct(int id)
        {
            DataTable table = SqlHelper.ExecuteDataTable("GET_UNIT_NAME_PRODUC", CommandType.StoredProcedure,
                "@ID", id);
            if (table != null && table.Rows.Count >= 0)
            {
                DataRow row = table.Rows[0];
                return row["NAME"].ToString();
            }
            return "";
        }

        public bool insertProperties(int pro_id, int quan)
        {
            return SqlHelper.ExecuteNonQuery("INSERT_PROPERTIES", CommandType.StoredProcedure,
                "@PRO_ID", pro_id,
                "@QUAN", quan);
        }
        public DataTable getPropertiesInfor()
        {
            return SqlHelper.ExecuteDataTable("GET_PROPERTIES_INFO", CommandType.StoredProcedure);

        }

        public DataRow getProDuctById(int id)
        {
            DataTable table = SqlHelper.ExecuteDataTable("GET_PRODUCT_BY_ID", CommandType.StoredProcedure,
                "@ID", id);
            if (table != null && table.Rows.Count > 0)
            {
                return table.Rows[0];
            }
            return null;
        }

        public bool updateProduct(int id, string name, string producer, float price, string notes, string special, int unitID)
        {
            return SqlHelper.ExecuteNonQuery("UPDATE_PRODUCTS", CommandType.StoredProcedure,
                "@ID", id,
                "@NAME", name,
                "@PRICE", price,
                "@NOTES", notes,
                "@USR_ID", LoginDao.userID,
                "@SPECIAL", special,
                "@PRODUCER", producer,
                "@UNIT_ID",unitID);
        }

        public bool deleteProduct(int id)
        {
            return SqlHelper.ExecuteNonQuery("DELETE_PRODUCT", CommandType.StoredProcedure,
                "@ID", id);
        }

        public DataRow getPropertiesById(int id)
        {
            DataTable table = SqlHelper.ExecuteDataTable("GET_PROPERTIES_BY_ID", CommandType.StoredProcedure,
                "@ID", id);
            if (table != null && table.Rows.Count > 0)
            {
                return table.Rows[0];
            }
            return null;
        }

        public bool updateProperties(int id, int producId, int quantity)
        {
            return SqlHelper.ExecuteNonQuery("UPDATE_PROPERTIES", CommandType.StoredProcedure,
                "@ID", id,
                "@PRO_ID", producId,
                "@QUA", quantity);
        }

        public bool deleteProperties(int id)
        {
            return SqlHelper.ExecuteNonQuery("DELETE_PROPERTIES", CommandType.StoredProcedure,
                "@ID", id);
        }
    }
}
